package com.erdos.common.util.excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.erdos.common.util.DatetimeUtil;


public class ExcelReader {
	
	private static final int MAX_COLUMN_NUM = 255;
	private static final Log logger = LogFactory.getLog(ExcelReader.class);         
    /**
     * 
     * @param fileName
     * @return
     */
    public static String[] getSheetNames(String fileName)
	{
		String[] sheetNames = null;

		try
		{		
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			
			int sheetCount = wb.getNumberOfSheets();
			sheetNames = new String[sheetCount];
			for(int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex ++)
			{
				sheetNames[sheetIndex] = wb.getSheetName(sheetIndex);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return sheetNames;
	}
    
    /**
     * 
     * @param inputStream
     * @return
     */
    public static String[] getSheetNames(InputStream inputStream)
    {
        String[] sheetNames = null;

        try
        {       
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            
            int sheetCount = wb.getNumberOfSheets();
            sheetNames = new String[sheetCount];
            for(int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex ++)
            {
                sheetNames[sheetIndex] = wb.getSheetName(sheetIndex);
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        
        return sheetNames;
    }
    
    /**
     * 
     * @param fileName
     * @param sheetName
     * @return
     */
	public static TableModel read(String fileName, String sheetName)
	{
		DefaultTableModel tableModel = null;
		try
		{		
			int rowCount = 0;
			int colCount = 0;

			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName));
			//POIFSFileSystem fs = new POIFSFileSystem(ExcelReader.class.getResourceAsStream(fileName));
			
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheet(sheetName);

			rowCount = sheet.getLastRowNum() + 1; 

			for(int rowIndex = 0; rowIndex < rowCount; rowIndex ++)
			{
				HSSFRow row = sheet.getRow(rowIndex);
				if(row != null && row.getLastCellNum() > colCount)
				{
					colCount = row.getPhysicalNumberOfCells();
                    logger.debug("colCount: "+ colCount);
				}
			}

            logger.debug("final colCount: "+ colCount);
			tableModel = new DefaultTableModel(rowCount, colCount);

			//List formats = HSSFDataFormat.getBuiltinFormats();

			for(int rowIndex = 0; rowIndex < rowCount; rowIndex ++)
			{
				HSSFRow row = sheet.getRow(rowIndex);
				if(row != null)
				{
					for(int colIndex = 0; colIndex < colCount; colIndex ++)
					{
						HSSFCell cell = row.getCell((short) colIndex);
						
						if(cell != null)
						{
							Object value = null;
							
							switch(cell.getCellType())
							{
								case HSSFCell.CELL_TYPE_BOOLEAN :
									value = new Boolean(cell.getBooleanCellValue());
									break;

								case HSSFCell.CELL_TYPE_NUMERIC :
									value = new Double(cell.getNumericCellValue());
									short format = cell.getCellStyle().getDataFormat();
									if ((0xe <= format && format <= 0x16) 
										|| (0x17 <= format && format <= 0x24)
										|| (0x2d <= format && format <= 0x2f))
									{
										value = cell.getDateCellValue();
									}
									break;

								case HSSFCell.CELL_TYPE_FORMULA :
									value = new Double(cell.getNumericCellValue());
									if(Double.isNaN(((Double) value).doubleValue()))
									{
										value = cell.getStringCellValue();
									}
									break;

								default : 
									value = cell.getStringCellValue();
							}
						
//							System.out.println("[ExcelReader.readAsTableModel] [" + rowIndex + ", " + colIndex + "]=" + value);
							tableModel.setValueAt(value, rowIndex, colIndex);
						}
					}
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return tableModel;
	}
    
    /**
     * 
     * @param inputStream
     * @param sheetName
     * @return
     */
    public static TableModel read(InputStream inputStream, String sheetName)
    {
        DefaultTableModel tableModel = null;
        try
        {       
            int rowCount = 0;
            int colCount = 0;

            POIFSFileSystem fs = new POIFSFileSystem(inputStream);

            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheet(sheetName);

            rowCount = sheet.getLastRowNum() + 1; 

            for(int rowIndex = 0; rowIndex < rowCount; rowIndex ++)
            {
                HSSFRow row = sheet.getRow(rowIndex);
                if(row != null && row.getLastCellNum() > colCount)
                {
                    colCount = row.getPhysicalNumberOfCells();
                    logger.debug("colCount: "+ colCount);
                }
            }
            
            logger.debug("final colCount: "+ colCount);
            tableModel = new DefaultTableModel(rowCount, colCount);

           // List formats = HSSFDataFormat.getBuiltinFormats();

            for(int rowIndex = 0; rowIndex < rowCount; rowIndex ++)
            {
                HSSFRow row = sheet.getRow(rowIndex);
                if(row != null)
                {
                    for(int colIndex = 0; colIndex < colCount; colIndex ++)
                    {
                        HSSFCell cell = row.getCell((short) colIndex);
                        
                        if(cell != null)
                        {
                            Object value = null;
                            
                            switch(cell.getCellType())
                            {
                                case HSSFCell.CELL_TYPE_BOOLEAN :
                                    value = new Boolean(cell.getBooleanCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC :
                                    value = new Double(cell.getNumericCellValue());
                                    short format = cell.getCellStyle().getDataFormat();
                                    if ((0xe <= format && format <= 0x16) 
                                        || (0x17 <= format && format <= 0x24)
                                        || (0x2d <= format && format <= 0x2f))
                                    {
                                        value = cell.getDateCellValue();
                                    }
                                    break;

                                case HSSFCell.CELL_TYPE_FORMULA :
                                    value = new Double(cell.getNumericCellValue());
                                    if(Double.isNaN(((Double) value).doubleValue()))
                                    {
                                        value = cell.getStringCellValue();
                                    }
                                    break;

                                default : 
                                    value = cell.getStringCellValue();
                            }
                        
                            //System.out.println("[ExcelReader.readAsTableModel] [" + rowIndex + ", " + colIndex + "]=" + value);
                            tableModel.setValueAt(value, rowIndex, colIndex);
                        }
                    }
                }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return tableModel;
    }
    
    public static Object getCellValue(HSSFCell cell) {
		if (cell == null) {
			return null;
		}

		Object value = null;

		switch (cell.getCellType()) {

		case HSSFCell.CELL_TYPE_BOOLEAN:
			value = new Boolean(cell.getBooleanCellValue());
			break;

		case HSSFCell.CELL_TYPE_NUMERIC:
			value = new Double(cell.getNumericCellValue());
			short format = cell.getCellStyle().getDataFormat();
			if ((0xe <= format && format <= 0x16) || (0x17 <= format && format <= 0x24)
					|| (0x2d <= format && format <= 0x2f)) {
				value = cell.getDateCellValue();
			}
			break;

		case HSSFCell.CELL_TYPE_FORMULA:
			value = new Double(cell.getNumericCellValue());
			if (Double.isNaN(((Double) value).doubleValue())) {
				value = cell.getStringCellValue();
			}
			break;

		default:
			value = cell.getStringCellValue();
		}
		return value;
	}

    public static Object getValue(HSSFRow row, int cellIndex) {
    	HSSFCell cell = row.getCell((short) cellIndex);
    	return (cell == null ? null : getCellValue(cell));
    }
	@SuppressWarnings("unchecked")
    public static List getRowValues(HSSFRow row, int startIndex, int endIndex) {
    	if (startIndex > MAX_COLUMN_NUM) {
    		throw new IllegalArgumentException("Start cell index can not be larger than "
					+ MAX_COLUMN_NUM);
    	}
    	if (startIndex < 0) {
    		startIndex = 0;
    	}
		if (endIndex < 0 || endIndex > MAX_COLUMN_NUM) {
			endIndex = MAX_COLUMN_NUM;
		}
		List result = new ArrayList(endIndex - startIndex);
		for (int i = startIndex; i < endIndex; i++) {
			HSSFCell cell = row.getCell((short) i);
			if (cell == null) {
				break;
			}
			Object value = getCellValue(cell);
			result.add(value);
		}
		return result;
	}
    
    public static List getRowValues(HSSFRow row) {
    	return getRowValues(row, 0, -1);
    }
    
    public static Map<String, List<Map<Integer, String>>> getDataListBySheet(
            InputStream inputStream) {
        Map<String, List<Map<Integer, String>>> sheetMap = new HashMap<String, List<Map<Integer, String>>>();
        try {
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            HSSFSheet sheet = null;
            HSSFRow row = null;
            HSSFCell cell = null;
            int sheetCount = workbook.getNumberOfSheets();
            String[] sheetNames = new String[sheetCount];
            for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
                sheetNames[sheetIndex] = workbook.getSheetName(sheetIndex);
            }
            for (int k = 0; k < sheetNames.length; k++) {
                List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();
                sheet = workbook.getSheet(sheetNames[k]);
                int totalRowCount = sheet.getPhysicalNumberOfRows();
                int totalColumnCount = sheet.getRow(0)
                        .getPhysicalNumberOfCells();
                Map<Integer, String> columnMap = null;
                for (int i = 0; i < totalRowCount; i++) {
                    columnMap = new HashMap<Integer, String>();
                    row = sheet.getRow(i);
                    if (row != null) {
                        for (int j = 0; j < totalColumnCount; j++) {
                            cell = row.getCell(j);
                            columnMap.put(j,
                                    transObjectToString(getCellValue1(cell)));
                        }
                        dataList.add(columnMap);
                    }
                }
                sheetMap.put(sheetNames[k], dataList);
            }

        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return sheetMap;
    }
    private static Object getCellValue1(HSSFCell cell) {
        if (cell == null)
            return "";
        int typeNum = cell.getCellType();

        switch (typeNum) {
        case HSSFCell.CELL_TYPE_NUMERIC: {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date d = cell.getDateCellValue();
                return DatetimeUtil.dateToString(d,
                        DatetimeUtil.YYYY_MM_DD_HH_MM_SS);
            } else {
                String str = cell.getNumericCellValue() + "";
                Pattern p = Pattern.compile("\\w+\\.0");
                Matcher matcher = p.matcher(str);
                if (matcher.matches())
                    return str.substring(0, str.length() - 2);
                return cell.getNumericCellValue();
            }
        }
        case HSSFCell.CELL_TYPE_STRING:
            return cell.getRichStringCellValue() == null ? "" : cell
                    .getRichStringCellValue();
        }
        return null;
    }
    public static String transObjectToString(Object obj) {
        boolean bNull = (obj == null || "null".equals(obj));
        return bNull == true ? "" : obj.toString().trim();
    }
}

